<?php
/**
 * Blog
 *
 * @copyright        2017 opencart.cn - All Rights Reserved
 * @link             http://www.guangdawangluo.com
 * @author           Sam Chen <sam.chen@opencart.cn>
 * @created          2017-05-22 14:32:53
 * @modified         2017-05-26 17:18:16
 */

class ModelBlogBlog extends Model {
	public function getCategory($category_id) {
		$query = $this->db->query("SELECT DISTINCT * FROM " . DB_PREFIX . "blog_category bc LEFT JOIN " . DB_PREFIX . "blog_category_description bcd ON (bcd.category_id = bc.category_id) LEFT JOIN " . DB_PREFIX . "blog_category_to_store bc2s ON (bc2s.category_id = bc.category_id) WHERE bc.category_id = '" . (int)$category_id . "' AND bc.status = '1' AND bcd.language_id = '" . $this->config->get('config_language_id') . "' AND bc2s.store_id = '" . $this->config->get('config_store_id') . "'");

		return $query->row;
	}

	public function getPosts($data = array()) {
		$sql = "SELECT bp.*, bpd.* FROM " . DB_PREFIX . "blog_post bp LEFT JOIN " . DB_PREFIX . "blog_post_description bpd ON (bp.post_id = bpd.post_id)";

		if (isset($data['category_id']) && (int)$data['category_id']) {
			$sql .= " LEFT JOIN " . DB_PREFIX . "blog_post_to_category bp2c ON (bp.post_id = bp2c.post_id)";
		}

		$sql .= " WHERE bp.status = '1' AND bp.date_added <= NOW() AND bpd.language_id = '" . (int)$this->config->get('config_language_id') . "'";

		if (isset($data['category_id']) && (int)$data['category_id']) {
			$sql .= " AND bp2c.category_id = '" . (int)$data['category_id'] . "'";
		}

		$sql .= " ORDER BY bp.sort_order,bp.date_added DESC";

		if (isset($data['start']) || isset($data['limit'])) {
			if ($data['start'] < 0) {
				$data['start'] = 0;
			}

			if ($data['limit'] < 1) {
				$data['limit'] = 10;
			}

			$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
		}

		$query = $this->db->query($sql);

		return $query->rows;
	}

	public function getTotalPosts($data = array()) {
		$sql = "SELECT COUNT(DISTINCT bp.post_id) AS total FROM " . DB_PREFIX . "blog_post bp";

		if (isset($data['category_id']) && (int)$data['category_id']) {
			$sql .= " LEFT JOIN " . DB_PREFIX . "blog_post_to_category bpc ON (bp.post_id = bpc.post_id) WHERE bp.status = '1' AND bpc.category_id = '" . (int)$data['category_id'] . "'";
		}

		$query = $this->db->query($sql);

		return $query->row['total'];
	}

	public function getPost($post_id) {
		$query = $this->db->query("SELECT bp.*, bpd.* FROM " . DB_PREFIX . "blog_post bp LEFT JOIN " . DB_PREFIX . "blog_post_description bpd ON (bp.post_id = bpd.post_id) WHERE bp.post_id = '" . (int)$post_id . "' AND bp.status = '1'  AND bpd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND bp.date_added <= NOW()");


		return $query->row;
	}

	public function getPostCategories($post_id) {
		$sql = "SELECT bcd.* FROM " . DB_PREFIX . "blog_post_to_category bp2c LEFT JOIN " . DB_PREFIX . "blog_category bc ON (bc.category_id = bp2c.category_id) LEFT JOIN " . DB_PREFIX . "blog_category_description bcd ON (bcd.category_id = bc.category_id) LEFT JOIN " . DB_PREFIX . "blog_category_to_store bc2s ON (bc2s.category_id = bc.category_id) WHERE post_id = '" . (int)$post_id . "' AND bc.status = '1' AND bcd.language_id = '" . $this->config->get('config_language_id') . "' AND bc2s.store_id = '" . $this->config->get('config_store_id') . "' ORDER BY bc.sort_order DESC";

		$query = $this->db->query($sql);
		return $query->rows;
	}

	public function getTopCategories($data = array()) {
		$sql = "SELECT * FROM " . DB_PREFIX . "blog_category bc LEFT JOIN " . DB_PREFIX . "blog_category_description bcd ON (bc.category_id = bcd.category_id) LEFT JOIN " . DB_PREFIX . "blog_category_to_store bc2s ON (bc2s.category_id = bc.category_id) WHERE bc.status = '1' AND bc.top = '1' AND bc2s.store_id = '" . $this->config->get('config_store_id') . "' AND bcd.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY bc.sort_order DESC";

		$query = $this->db->query($sql);

		return $query->rows;
	}

	public function getAllCategories() {
		$sql = "SELECT * FROM " . DB_PREFIX . "blog_category bc LEFT JOIN " . DB_PREFIX . "blog_category_description bcd ON (bc.category_id = bcd.category_id) LEFT JOIN " . DB_PREFIX . "blog_category_to_store bc2s ON (bc2s.category_id = bc.category_id) WHERE bc.status = '1' AND bc2s.store_id = '" . $this->config->get('config_store_id') . "' AND bcd.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY bc.sort_order DESC";

		$query = $this->db->query($sql);

		return $query->rows;
	}

	public function getLatestPosts($limit = 10) {
		$sql = "SELECT bpd.post_id,bpd.name,bpd.meta_title,bpd.author,bp.date_added FROM " . DB_PREFIX . "blog_post bp LEFT JOIN " . DB_PREFIX . "blog_post_description bpd ON (bp.post_id = bpd.post_id) WHERE bp.status = '1' AND bp.date_added <= NOW() AND bpd.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY bp.sort_order,bp.date_added DESC LIMIT 0," . (int)$limit;
		$query = $this->db->query($sql);

		return $query->rows;
	}
}
